﻿using System;
using System.Data;
using System.Data.Common;
using EastFantasy.DnfAccountScan.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace EastFantasy.DnfAccountScan.DAL
{
    public class AccountDal
    {
        #region SqlServer数据库连接
        private readonly Database sqlServerDb = DatabaseFactory.CreateDatabase("DnfAccountScan");
        
        #endregion

        #region 上传正确的账号到数据库
        /// <summary>
        /// 上传正确的账号到数据库
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool Upload(Account account)
        {
            using (DbConnection connection = sqlServerDb.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    const string sqlStr = @"INSERT INTO AccountScan
                                                    ( ID ,
                                                      Server ,
                                                      Account ,
                                                      Password ,
                                                      Level ,
                                                      IP ,
                                                      IsRight ,
                                                      LeaverBuster ,
                                                      QA ,
                                                      IsQA ,
                                                      CreateDate
                                                    )
                                            VALUES  ( @ID , -- ID - uniqueidentifier
                                                      @Server , -- Server - nvarchar(10)
                                                      @Account , -- Account - smallint
                                                      @Password , -- Password - nvarchar(20)
                                                      @Level , -- Level - smallint
                                                      @IP , -- IP - nvarchar(15)
                                                      @IsRight , -- IsRight - nvarchar(10)
                                                      @LeaverBuster , -- LeaverBuster - nvarchar(10)
                                                      @QA , -- QA - nvarchar(100)
                                                      @IsQA , -- IsQA - nvarchar(10)
                                                      CONVERT(SMALLDATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+08:00'))  -- CreateDate - nvarchar(20)
                                                    ) ";
                    DbCommand dbCommand = sqlServerDb.GetSqlStringCommand(sqlStr);
                    sqlServerDb.AddInParameter(dbCommand, "ID", DbType.String, account.ID);
                    sqlServerDb.AddInParameter(dbCommand, "Server", DbType.String, account.Server);
                    sqlServerDb.AddInParameter(dbCommand, "Account", DbType.String, account.AccountName);
                    sqlServerDb.AddInParameter(dbCommand, "Password", DbType.String, account.Password);
                    sqlServerDb.AddInParameter(dbCommand, "Level", DbType.String, account.Level);
                    sqlServerDb.AddInParameter(dbCommand, "IP", DbType.String, account.IP);
                    sqlServerDb.AddInParameter(dbCommand, "IsRight", DbType.String, account.IsRight);
                    sqlServerDb.AddInParameter(dbCommand, "LeaverBuster", DbType.String, account.LeaverBuster);
                    //sqlServerDb.AddInParameter(dbCommand, "QA", DbType.String, account.QA);
                    sqlServerDb.AddInParameter(dbCommand, "IsQA", DbType.String, account.IsQA);
                    sqlServerDb.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            return true;
        }
        #endregion

        #region 获取服务器的正确账号
        /// <summary>
        /// 获取服务器的正确账号
        /// </summary>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="server">大区</param>
        /// <param name="level">等级</param>
        /// <returns></returns>
        public DataTable GetAccountByCondition(string start, string end, string server, string level)
        {
            const string sqlStr = @"SELECT  * ,
                                            CONVERT(SMALLDATETIME, CreateDate) AS Date
                                    FROM    AccountScan
                                    WHERE   Server LIKE @SERVER
                                            AND Level >= @Level
                                            AND CreateDate BETWEEN @Start AND @End ";
            DbCommand dbCommand = sqlServerDb.GetSqlStringCommand(sqlStr);
            sqlServerDb.AddInParameter(dbCommand, "SERVER", DbType.String, "%" + server + "%");
            sqlServerDb.AddInParameter(dbCommand, "Level", DbType.String, level);
            sqlServerDb.AddInParameter(dbCommand, "Start", DbType.String, start);
            sqlServerDb.AddInParameter(dbCommand, "End", DbType.String, end);
            DataTable dt = sqlServerDb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        #endregion
    }
}
